/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package br.com.foxline.service;

import br.com.foxline.enums.TypeDocument;
import br.com.foxline.model.CharterSDI;
import br.com.foxline.model.Part;
import br.com.foxline.model.Stamp;
import br.com.foxline.model.People;
import br.com.foxline.util.Caracter;
import java.util.List;
import javax.ejb.LocalBean;
import javax.ejb.Stateless;
import javax.persistence.Query;

/**
 *
 * @author roniere
 */
@Stateless
@LocalBean
public class CharterSDIService extends GenericService<CharterSDI> {

    public CharterSDIService() {
        super(CharterSDI.class);
    }

    public List<CharterSDI> searchCharter(CharterSDI charter, Part part, Stamp stamp) {

        Query query;
          String sql = "Select distinct(c) from CharterSDI c ";

     

        if (charter.getActCep() != null) {
            sql += " JOIN c.ActCep ac  ";
        }
        if (part != null) {
            boolean q = true;
            if (part.getPeople() != null) {
                if ((part.getPeople().getName() != null && !part.getPeople().getName().isEmpty())
                        || (part.getPeople().getTipoDocumento() == TypeDocument.CPF && part.getPeople().getCpf() != null && !part.getPeople().getCpf().isEmpty())
                        || (part.getPeople().getTipoDocumento() == TypeDocument.CNPJ && part.getPeople().getCnpj() != null && !part.getPeople().getCnpj().isEmpty())) {
                    sql += "  JOIN c.parts p  JOIN p.people peo ";
                    q = false;
                }
            }
            if (part.getPartQuality() != null) {
                if (q) {
                    sql += "  JOIN c.parts p  ";
                }
                sql += " JOIN p.partQuality pq  ";
            }
        } 
        
        
        if (stamp != null && stamp.getTypeStamp() != null) {
            sql += " JOIN c.stamp st JOIN st.typeStamp ts";

        }

        sql += " where ";

        if (charter.getActCep() != null) {
            sql += " ac.id = :idActCep and ";
        }
        
        if (charter.getBook() != null && !charter.getBook().isEmpty()) {
            sql += " c.book like :book  and ";
        }
        if (charter.getBookComp() != null && !charter.getBookComp().isEmpty()) {
            sql += " c.bookComp like :bookComp  and ";
        }
        if (charter.getLeaf() != null && !charter.getLeaf().isEmpty()) {
            sql += " c.leaf like :leaf and ";
        }
        if (charter.getLeafComp() != null && !charter.getLeafComp().isEmpty()) {
            sql += " c.leafComp like :leafComp and ";
        }
        if (charter.getDateAct() != null) {
            sql += " c.dateAct = :dateAct and ";
        }

        if (part != null) {
            if (part.getPartQuality() != null) {
                sql += " pq.id = :partQuality and ";
            }
            People people = part.getPeople();
            if (people != null) {
                
               
                if (people.getName() != null && !people.getName().isEmpty()) {
                    sql += " lower(peo.name) like lower(:name) and ";
                }
                if (people.getTipoDocumento() == TypeDocument.CPF && people.getCpf() != null && !people.getCpf().isEmpty()) {
                    sql += " lower(peo.cpf) like lower(:doc)  and ";
                } else if (people.getTipoDocumento() == TypeDocument.CNPJ && people.getCnpj() != null && !people.getCnpj().isEmpty()) {
                    sql += " lower(peo.cnpj) like lower(:doc)  and ";
                }
                

            }
        }
        if (stamp != null) {
            if (stamp.getSerie() != null && !stamp.getSerie().isEmpty()) {
                sql += " st.serie like :serie and ";
            }
            if (stamp.getNumber() != null) {
                sql += " st.number = :number and ";
            }
            if (stamp.getTypeStamp() != null) {
                sql += " ts.id = :idTypeStamp  and ";
            }
        }

        sql += " 1=1 ";

        query = getEntityManager().createQuery(sql);
        if (charter.getActCep() != null) {
            query.setParameter("idActCep", charter.getActCep().getId());
        }
        
     
        if (charter.getBook() != null && !charter.getBook().isEmpty()) {
            query.setParameter("book", charter.getBook());
        }
        if (charter.getBookComp() != null && !charter.getBookComp().isEmpty()) {
            query.setParameter("bookComp", charter.getBookComp());
        }
       

        if (charter.getLeaf() != null && !charter.getLeaf().isEmpty()) {
            query.setParameter("leaf", charter.getLeaf());
        }
        if (charter.getLeafComp() != null && !charter.getLeafComp().isEmpty()) {
            query.setParameter("leafComp", charter.getLeaf());
        }
        if (charter.getDateAct() != null) {
            query.setParameter("dateAct", charter.getDateAct());
        }
        if (part != null) {
            
            if (part.getPartQuality() != null) {
                query.setParameter("partQuality", part.getPartQuality().getId());
            }
            if (part.getPeople() != null) {
                if (part.getPeople().getName() != null && !part.getPeople().getName().isEmpty()) {
                    query.setParameter("name", part.getPeople().getName() + "%");
                }
                if ((part.getPeople().getCpf() != null && !part.getPeople().getCpf().isEmpty())
                        || (part.getPeople().getCnpj() != null && !part.getPeople().getCnpj().isEmpty())) {
                    query.setParameter("doc",
                            part.getPeople().getTipoDocumento() == TypeDocument.CPF ? Caracter.removecaracter(part.getPeople().getCpf()) : Caracter.removecaracter(part.getPeople().getCnpj()));
                }
            }
        }
        if (stamp != null) {
            if (stamp.getSerie() != null && !stamp.getSerie().isEmpty()) {
                query.setParameter("serie", stamp.getSerie());
            }
            if (stamp.getNumber() != null) {
                query.setParameter("number", stamp.getNumber());
            }
            if (stamp.getTypeStamp() != null) {
                query.setParameter("idTypeStamp", stamp.getTypeStamp().getId());
            }
        }
        System.err.println(sql + "");
        return query.getResultList();
    }
}
